package inet.db.tennis;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import inet.db.pool.*;
import inet.util.Logger;

public class TennisPhaseDAO {

	private Logger logger = null;
	private DBPoolX poolX = null;
	public TennisPhaseDAO(){
		logger = new Logger(this.getClass().getName());
		try{
			poolX = DBPoolX.getInstance(DBPoolXName.SERVICE_BONGDA);
		}catch(Exception exception){
			logger.log(exception.toString()); 
		}
	}
	
	/**
	 * function get a TennisPhase by id
	 * @param id
	 * @return
	 */
    public TennisPhase getRow(BigDecimal id) {
        Connection conn = null;
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        String strSQL = null;
        TennisPhase phase = null;
        try {
            conn = poolX.getConnection();
            strSQL =
            	" select a.ID, a.CHAMPION_CODE, a.SEASON_ID, PHASE_NAME, a.GEN_DATE, b.NAME, c.SEASON_NAME " +
    			" from TENNIS_PHASE a join TENNIS_CHAMPION b ON a.CHAMPION_CODE = b.CODE join TENNIS_SEASON c on a.SEASON_ID = c.ID " +
    			" where a.ID = ? ";
            preStmt = conn.prepareStatement(strSQL);
            preStmt.setBigDecimal(1, id);
            rs = preStmt.executeQuery();
            if (rs.next()) {
            	phase = new TennisPhase();
            	phase.setId(rs.getBigDecimal(1));
            	phase.setChampionCode(rs.getString(2));
            	phase.setSeasonId(rs.getBigDecimal(3));
            	phase.setPhaseName(rs.getString(4));
            	phase.setGenDate(rs.getTimestamp(5));
            	phase.setChampionName(rs.getString(6));
    			phase.setSeasonName(rs.getString(7));
            }
        }
        catch (SQLException e) {
            logger.error("getRow: Error executing SQL " + strSQL + ">>>" + e.toString());
        } finally {
            poolX.releaseConnection(conn, preStmt, rs);
        }
        return phase;
    }
    
    /**
     * fucntion get all phase
     * @return Vector<TennisPhase> 
     * TennisPhase (ID, CHAMPION_CODE, SEASON_ID, PHASE_NAME, GEN_DATE)
     */
    public Vector<TennisPhase> findAll() {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisPhase phase = null;
    	Vector <TennisPhase> vPhase = null;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			"select ID, CHAMPION_CODE, SEASON_ID, PHASE_NAME, GEN_DATE from TENNIS_PHASE ";
    		preStmt = conn.prepareStatement(strSQL);
    		rs = preStmt.executeQuery();
    		vPhase = new Vector<TennisPhase>();
    		while (rs.next()) {
    			phase = new TennisPhase();
    			phase.setId(rs.getBigDecimal(1));
            	phase.setChampionCode(rs.getString(2));
            	phase.setSeasonId(rs.getBigDecimal(3));
            	phase.setPhaseName(rs.getString(4));
            	phase.setGenDate(rs.getTimestamp(5));
    			
    			vPhase.addElement(phase);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return vPhase;
    }
    
    /**
     * function find TennisPhase by champion code and season id.
     * @param championCode
     * @param seasonId
     * @return Vector<TennisPhase>
     */
    public Vector<TennisPhase> findAll(String championCode, BigDecimal seasonId) {
    	if(championCode == null || seasonId == null) return null;
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisPhase phase = null;
    	Vector <TennisPhase> vPhase = null;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			" select a.ID, a.CHAMPION_CODE, a.SEASON_ID, PHASE_NAME, a.GEN_DATE, b.NAME, c.SEASON_NAME " +
    			" from TENNIS_PHASE a join TENNIS_CHAMPION b ON a.CHAMPION_CODE = b.CODE join TENNIS_SEASON c on a.SEASON_ID = c.ID " +
    			" where a.CHAMPION_CODE = ? and a.SEASON_ID = ? ";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setString(1, championCode);
    		preStmt.setBigDecimal(2, seasonId);
    		
    		rs = preStmt.executeQuery();
    		vPhase = new Vector<TennisPhase>();
    		while (rs.next()) {
    			phase = new TennisPhase();
    			phase.setId(rs.getBigDecimal(1));
    			phase.setChampionCode(rs.getString(2));
    			phase.setSeasonId(rs.getBigDecimal(3));
    			phase.setPhaseName(rs.getString(4));
    			phase.setGenDate(rs.getTimestamp(5));
    			phase.setChampionName(rs.getString(6));
    			phase.setSeasonName(rs.getString(7));
    			
    			vPhase.addElement(phase);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
    		System.out.println("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return vPhase;
    }
    
    /**
     * function get TennisPhase by championCode and Season id
     * @param championCode
     * @param seasonId
     * @return Vector<TennisPhase>
     */
    @Deprecated
    public Vector<TennisPhase> findPhaseByChampion(String championCode, BigDecimal seasonId) {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisPhase phase = null;
    	Vector <TennisPhase> vPhase = null;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			"select ID, CHAMPION_CODE, SEASON_ID, PHASE_NAME, GEN_DATE from TENNIS_PHASE where CHAMPION_CODE = ? and SEASON_ID = ? ";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setString(1, championCode);
    		preStmt.setBigDecimal(2, seasonId);
    		
    		rs = preStmt.executeQuery();
    		vPhase = new Vector<TennisPhase>();
    		while (rs.next()) {
    			phase = new TennisPhase();
    			phase.setId(rs.getBigDecimal(1));
    			phase.setChampionCode(rs.getString(2));
    			phase.setSeasonId(rs.getBigDecimal(3));
    			phase.setPhaseName(rs.getString(4));
    			phase.setGenDate(rs.getTimestamp(5));
    			
    			vPhase.addElement(phase);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return vPhase;
    }
	
    /**
     * function insert a TennisPhase
     * @param championCode
     * @param seasonId
     * @param phaseName
     * @return true if insert success else return false.
     */
	public boolean insertRow(String championCode, BigDecimal seasonId, String phaseName){
		Connection conn = null;
        PreparedStatement preStmt = null;
        boolean result = false;
        String strSQL = null;
        try {
            conn = poolX.getConnection();

        	strSQL =" insert into TENNIS_PHASE( ID, CHAMPION_CODE, SEASON_ID, PHASE_NAME, GEN_DATE ) " +
    				" values(TENNIS_PHASE_SEQ.nextval, ?, ?, ?, sysdate) ";
	        preStmt = conn.prepareStatement(strSQL);
	        preStmt.setString(1, championCode);
	        preStmt.setBigDecimal(2, seasonId);
	        preStmt.setString(3, phaseName);
	        
	        if (preStmt.executeUpdate()>=1) {
	        	result = true;
	        }
        } catch (Exception ex) {
        	logger.error("insertRow: Error executing SQL " + strSQL + ">>>" + ex.toString());            
        } finally {
        	poolX.releaseConnection(conn, preStmt);
       }
       return result;
	}
	
	/**
	 * function update TennisPhase
	 * @param id
	 * @param phaseName
	 * @return true if update success else return false.
	 */
	public boolean updateRow(BigDecimal id, String phaseName){
		Connection conn = null;
        PreparedStatement preStmt = null;
        boolean result = false;
        String strSQL = null;
        try {
        	conn = poolX.getConnection();
        	strSQL =" update TENNIS_PHASE set PHASE_NAME = ? " +
        			" where ID = ?";
        	preStmt = conn.prepareStatement(strSQL);
        	preStmt.setString(1, phaseName);
        	preStmt.setBigDecimal(2, id);
        	
        	if(preStmt.executeUpdate() > 0){
        		result = true;
        	}
        } catch (Exception ex) {
        	logger.error("updateRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
        } finally {
        	poolX.releaseConnection(conn, preStmt);
       }
        return result;
	}	

	/**
	 * function delete a TennisPhase
	 * @param id
	 * @return true if update success else return false.
	 */
	public boolean deleteRow(BigDecimal id){
		Connection conn = null;
		PreparedStatement preStmt = null;
		boolean result = false;
		String strSQL = null;
		try {
			conn = poolX.getConnection();
			strSQL =" delete from TENNIS_PHASE where ID = ? ";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setBigDecimal(1, id);
			if(preStmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception ex) {
			logger.error("deleteRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt);
		}
		return result;
	}
	
	 /**
     * function get TennisPhase by championCode 
     * @param championCode
     * @return Vector<TennisPhase>
     * Author : Hoannd
     */
  
    public Vector<TennisPhase> findPhaseByChampionCode(String championCode) {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisPhase phase = null;
    	Vector <TennisPhase> vPhase = null;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			"select ID, CHAMPION_CODE, SEASON_ID, PHASE_NAME, GEN_DATE from TENNIS_PHASE where CHAMPION_CODE = ?  ";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setString(1, championCode);
    		
    		rs = preStmt.executeQuery();
    		vPhase = new Vector<TennisPhase>();
    		while (rs.next()) {
    			phase = new TennisPhase();
    			phase.setId(rs.getBigDecimal(1));
    			phase.setChampionCode(rs.getString(2));
    			phase.setSeasonId(rs.getBigDecimal(3));
    			phase.setPhaseName(rs.getString(4));
    			phase.setGenDate(rs.getTimestamp(5));
    			
    			vPhase.addElement(phase);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return vPhase;
    }
    
    /**
	 * function get id of phase.
	 * @param id
	 * @param status
	 * @return true if update successfull else return false.
	 * Author : Hoannd
	 */
	public BigDecimal getPhasedCurrentId(BigDecimal id, String  championCode){
		Connection conn = null;
		PreparedStatement preStmt = null;
		String strSQL = null;
		boolean result = false;
		ResultSet rs = null;
		BigDecimal currId = null ;
		try {
			conn = poolX.getConnection();
			strSQL =
				" Select id TENNIS_PHASE where lower(CHAMPION_CODE) = ? and id = ?" ;
			
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setBigDecimal(1, id);
			preStmt.setString(2, championCode.toLowerCase());
			preStmt = conn.prepareStatement(strSQL);
			rs = preStmt.executeQuery();
			if (rs.next()) {
				currId = rs.getBigDecimal(1);
			} 
			
		} catch (Exception ex) {
			logger.error("updateScore: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt, rs);
		}
		return currId;
	}
	
	
	
	 public Vector<TennisPhase> findPhaseByCurrPhase(BigDecimal id, String championCode) {
	    	Connection conn = null;
	    	PreparedStatement preStmt = null;
	    	ResultSet rs = null;
	    	String strSQL = null;
	    	TennisPhase phase = null;
	    	Vector <TennisPhase> vPhase = null;
	    	
	    	try {
	    		conn = poolX.getConnection();
	    		strSQL =
	    			"select ID, CHAMPION_CODE from TENNIS_PHASE where id = ? and  lower(CHAMPION_CODE) = ?  ";
	    		preStmt = conn.prepareStatement(strSQL);
	    		preStmt.setBigDecimal(1, id);
	    		preStmt.setString(2, championCode.toLowerCase());
	    		rs = preStmt.executeQuery();
	    		vPhase = new Vector<TennisPhase>();
	    		while (rs.next()) {
	    			phase = new TennisPhase();
	    			phase.setId(rs.getBigDecimal(1));
	    			phase.setChampionCode(rs.getString(2));
	    			vPhase.addElement(phase);
	    		}
	    	}
	    	catch (SQLException e) {
	    		logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
	    	} finally {
	    		poolX.releaseConnection(conn, preStmt, rs);
	    	}
	    	return vPhase;
	    }
	    
    
}
